﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Text;
using System.Windows.Forms;
using AutoShutdownDotNet.Models;

namespace AutoShutdownDotNet.DataAccess
{
    public class Database
    {
        public static string DatabaseFilename = "database.db";
        
        private SQLiteConnection databaseConnection = new SQLiteConnection();
        private string _lastError = string.Empty;

        
        private const string Script_Task_Table = "CREATE TABLE Tasks (" +
                                                 "[TaskId] INTEGER PRIMARY KEY AUTOINCREMENT ," +
                                                 "[Title] VARCHAR, " +
                                                 "[TaskDate] DATETIME, " +
                                                 "[TaskHour] INTEGER NOT NULL, " +
                                                 "[TaskMinute] INTEGER NOT NULL, " +
                                                 "[Recur] INTEGER DEFAULT(0), " +
                                                 "[Prompt] INTEGER DEFAULT(0), " +
                                                 "[Action] INTEGER NOT NULL, " +
                                                 "[Timer] INTEGER DEFAULT(0), " +
                                                 "[Active] INTEGER DEFAULT(1), " +
                                                 "[FilePath] VARCHAR, " +
                                                 "[Message] VARCHAR, " +
                                                 "[Comment] VARCHAR, " +
                                                 "[CreatedDate] DATETIME NOT NULL, " +
                                                 "[LastModified] DATETIME NOT NULL)";

        private const string Script_History_Table = "CREATE TABLE History (" +
                                                    "[HistoryId] INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                                    "[TaskId] INTEGER, " +
                                                    "[Action] INTEGER, " +
                                                    "[ActionDate] DATETIME NOT NULL , " +
                                                    "[ActionHour] INTEGER NOT NULL, " +
                                                    "[ActionMinute] INTEGER NOT NULL, " +
                                                    "[Succeed] INTEGER DEFAULT(1), " +
                                                    "[FilePath] VARCHAR, " +
                                                    "[Comment] VARCHAR, " +
                                                    "FOREIGN KEY (TaskId) REFERENCES Tasks(TaskId))";

        private const string Script_Settings_Table = "CREATE TABLE Settings (" +
                                                     "[SettingId] INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                                     "[Startup] INTEGER DEFAULT(0), " +
                                                     "[Password] VARCHAR, " +
                                                     "[ListView] INTEGER DEFAULT(0)," +
                                                     "[WinState] INTEGER DEFAULT(0)," +
                                                     "[FirstRun] INTEGER DEFAULT(1), " +
                                                     "[FirstRunDate] DATETIME, " +
                                                     "[LastRunDate] DATETIME )";

        public string Filename { get; set; }
        public bool HasError { get { return _lastError != string.Empty; } }

        public string LastError
        {
            get { return _lastError; }
        }
        
        private string GetDatabasePath()
        {
            string dbPath = Application.StartupPath + "\\" + DatabaseFilename;
            return dbPath;
        }

        public Database()
        {
            Filename = GetDatabasePath();
            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder()
            {
                DataSource = Filename,
                DefaultTimeout = 10000
            };
            databaseConnection.ConnectionString = connectionString.ConnectionString + ";charset=utf8;";
            try
            {
                databaseConnection.Open();
            }
            catch (Exception ex)
            {
                _lastError = ex.GetBaseException().Message;
            }
        }

        ~Database()
        {
            if (databaseConnection.State != System.Data.ConnectionState.Closed)
                databaseConnection.Close();
        }

        public bool Create()
        {
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = Script_Settings_Table;
                try
                {
                    command.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    _lastError = ex.GetBaseException().Message;
                    return false;
                }

                command.CommandText = Script_Task_Table;
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    _lastError = ex.GetBaseException().Message;
                    return false;
                }
                command.CommandText = Script_History_Table;
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    _lastError = ex.GetBaseException().Message;
                    return false;
                }
                InsertDefaultSetting();
                return true;
            }
        }


        public void Insert(Task task)
        {
            string sql = @"INSERT INTO Tasks (Title, TaskDate, TaskHour, TaskMinute, Prompt, Action, Timer, Active, " +
                         "FilePath, Message, Comment, CreatedDate, LastModified, Recur) VALUES (@Title, @TaskDate, @TaskHour, " +
                         "@TaskMinute, @Prompt, @Action, @Timer, @Active, " +
                         "@FilePath, @Message, @Comment, @CreatedDate, @LastModified, @Recur)";
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Title", Value = task.Title });
                if(task.When.HasValue)
                    command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskDate", Value = task.When.Value.Date });
                else
                    command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskDate", Value = DBNull.Value });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskHour", Value = task.Hour });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskMinute", Value = task.Minute });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Prompt", Value = task.AskBefore });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Action", Value = task.Action });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Timer", Value = task.Timer });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Active", Value = task.Active ? 1 : 0 });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@FilePath", Value = task.FilePath });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Message", Value = task.Message });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Comment", Value = task.Comment });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@CreatedDate", Value = task.CreatedDate.Date });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@LastModified", Value = task.LastModified.Date });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Recur", Value = task.Recurrence });
                command.ExecuteNonQuery();
            }
        }

        public void Update(Task task)
        {
            string sql =
                @"UPDATE Tasks SET Title = @Title, TaskDate = @TaskDate, TaskHour = @TaskHour, TaskMinute = @TaskMinute, " +
                "Prompt = @Prompt, Action = @Action, Timer = @Timer, Active = @Active, FilePath = @FilePath, " +
                "Message = @Message, Comment = @Comment, LastModified = @LastModified, Recur = @Recur WHERE TaskId = @TaskId";
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Title", Value = task.Title });
                if (task.When.HasValue)
                    command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskDate", Value = task.When.Value.Date });
                else
                    command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskDate", Value = DBNull.Value });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskHour", Value = task.Hour });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskMinute", Value = task.Minute });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Prompt", Value = task.AskBefore });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Action", Value = task.Action });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Timer", Value = task.Timer });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Active", Value = task.Active ? 1 : 0 });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@FilePath", Value = task.FilePath });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Message", Value = task.Message });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Comment", Value = task.Comment });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@LastModified", Value = task.LastModified.Date });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskId", Value = task.Id });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Recur", Value = task.Recurrence });
                command.ExecuteNonQuery();
            }
        }

        public void Delete(Task task)
        {
            string sql = @"DELETE FROM Tasks WHERE TaskId = @TaskId";
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskId", Value = task.Id });
                command.ExecuteNonQuery();                
            }
        }

        public List<Task> Select(string title, DateTime? taskDate, AutoShutdownAction? action, bool? active, int? taskId)
        {
            List<Task> results = new List<Task>();
            string sql = @"SELECT * FROM Tasks";

            if (taskId.HasValue && taskId.Value > 0)
                sql += " WHERE TaskId = @TaskId";
            else if (title != string.Empty && taskDate == null && action == null && active == null)
                sql += " WHERE title like @title";
            else if (title != string.Empty && taskDate.HasValue && action == null && active == null)
                sql += " WHERE title like @title AND TaskDate = @TaskDate";

            return results;
        }

        public List<Task> SelectAllTasks(DateTime? from, DateTime? to)
        {
            List<Task> results = new List<Task>();
            string sql = @"SELECT * FROM Tasks";

            if (from.HasValue && to == null)
                sql += " WHERE TaskDate >= @FromDate";
            else if (from.HasValue && to.HasValue)
                sql += " WHERE TaskDate BETWEEN @FromDate AND @ToDate";
            else if (from == null && to.HasValue)
                sql += " WHERE TaskDate <= @ToDate";

            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                if (from != null)
                    command.Parameters.Add(new SQLiteParameter() { ParameterName = "@FromDate", Value = from.Value.Date });

                if (to != null)
                    command.Parameters.Add(new SQLiteParameter() { ParameterName = "@ToDate", Value = to.Value.Date });

                SQLiteDataReader recordset = command.ExecuteReader();
                while (recordset.Read())
                {
                    Task task = new Task();
                    task.Action = (AutoShutdownAction) Convert.ToInt32(recordset["Action"]);
                    task.Active = Convert.ToBoolean(recordset["Active"]);
                    task.AskBefore = Convert.ToBoolean(recordset["Prompt"]);
                    task.Comment = Convert.ToString(recordset["Comment"]);
                    task.CreatedDate = Convert.ToDateTime(recordset["CreatedDate"]);
                    task.FilePath = Convert.ToString(recordset["FilePath"]);
                    task.Hour = Convert.ToInt32(recordset["TaskHour"]);
                    task.Id = Convert.ToInt32(recordset["TaskId"]);
                    task.LastModified = Convert.ToDateTime(recordset["LastModified"]);
                    task.Message = Convert.ToString(recordset["Message"]);
                    task.Minute = Convert.ToInt32(recordset["TaskMinute"]);
                    task.Timer = Convert.ToInt32(recordset["Timer"]);
                    task.Title = Convert.ToString(recordset["Title"]);
                    task.Recurrence = (Recurrences) Convert.ToInt32(recordset["Recur"]);
                    if(recordset["TaskDate"] != DBNull.Value)
                        task.When = Convert.ToDateTime(recordset["TaskDate"]);
                    results.Add(task);
                }
                recordset.Close();
            }
            return results;
        }

        public bool Exist(int taskId, DateTime? when, int hour, int minute, Recurrences recurrences)
        {
            string sql = @"SELECT COUNT(TaskId) FROM Tasks WHERE TaskId <> @TaskId AND Recur = @Recur AND (TaskHour = @Hour AND TaskMinute = @Minute)";
            bool result = false;
            if (when.HasValue)
                sql += " AND TaskDate = @TaskDate";
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskId", Value = taskId });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Hour", Value = hour });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Minute", Value = minute });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Recur", Value = recurrences });
                if(when != null)
                    command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskDate", Value = when.Value.Date });

                int count = Convert.ToInt32(command.ExecuteScalar());
                result = Convert.ToBoolean(count);
            }
            return result;
        }

        public Task Load(int taskId)
        {
            Task task = null;
            string sql = @"SELECT * FROM Tasks WHERE TaskId = @TaskId";
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskId", Value = taskId });
                SQLiteDataReader recordset = command.ExecuteReader();
                while (recordset.Read())
                {
                    task = new Task();
                    task.Action = (AutoShutdownAction)Convert.ToInt32(recordset["Action"]);
                    task.Active = Convert.ToBoolean(recordset["Active"]);
                    task.AskBefore = Convert.ToBoolean(recordset["Prompt"]);
                    task.Comment = Convert.ToString(recordset["Comment"]);
                    task.CreatedDate = Convert.ToDateTime(recordset["CreatedDate"]);
                    task.FilePath = Convert.ToString(recordset["FilePath"]);
                    task.Hour = Convert.ToInt32(recordset["TaskHour"]);
                    task.Id = Convert.ToInt32(recordset["TaskId"]);
                    task.LastModified = Convert.ToDateTime(recordset["LastModified"]);
                    task.Message = Convert.ToString(recordset["Message"]);
                    task.Minute = Convert.ToInt32(recordset["TaskMinute"]);
                    task.Timer = Convert.ToInt32(recordset["Timer"]);
                    task.Title = Convert.ToString(recordset["Title"]);
                    task.Recurrence = (Recurrences)Convert.ToInt32(recordset["Recur"]);
                    if (recordset["TaskDate"] != DBNull.Value)
                        task.When = Convert.ToDateTime(recordset["TaskDate"]);
                }
                recordset.Close();
            }
            return task;
        }

        public void Insert(Setting setting)
        {
            string sql = @"INSERT INTO Settings (Startup, Password, FirstRun, FirstRunDate, LastRunDate) " +
                         "VALUES (@Startup, @Password, @FirstRun, @FirstRunDate, @LastRunDate)";
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Startup", Value = setting.StartWithWindows ? 1 : 0 });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Password", Value = setting.Password});
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@FirstRun", Value = setting.FirstRun ? 1 : 0 });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@FirstRunDate", Value = setting.FirstRunDate.Date });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@LastRunDate", Value = setting.LastRunDate.Date });
                command.ExecuteNonQuery();
            }
        }

        public void InsertDefaultSetting()
        {
            Setting setting = new Setting();
            setting.FirstRun = true;
            setting.FirstRunDate = DateTime.Now;
            setting.StartWithWindows = false;
            Insert(setting);
        }

        public void Update(Setting setting)
        {
            string sql = @"UPDATE Settings SET Startup = @Startup, Password = @Password, FirstRun = @FirstRun, " +
                         "FirstRunDate = @FirstRunDate, LastRunDate = @LastRunDate, ListView = @ListView, " +  
                         "WinState = @WinState WHERE SettingId = @SettingId";
            
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Startup", Value = setting.StartWithWindows ? 1 : 0 });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Password", Value = setting.Password });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@FirstRun", Value = setting.FirstRun ? 1 : 0 });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@FirstRunDate", Value = setting.FirstRunDate.Date });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@LastRunDate", Value = setting.LastRunDate.Date });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@ListView", Value = setting.View });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@WinState", Value = setting.WindowsState });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@SettingId", Value = setting.Id });
                command.ExecuteNonQuery();
            }
        }


        public Setting GetDefaultSetting()
        {
            Setting result = null;
            string sql = @"SELECT * FROM Settings";
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                SQLiteDataReader recordset = command.ExecuteReader();
                if (recordset.Read())
                    result = new Setting
                                 {
                                     FirstRun = Convert.ToBoolean(recordset["FirstRun"]),
                                     FirstRunDate = Convert.ToDateTime(recordset["FirstRunDate"]),
                                     Id = Convert.ToInt32(recordset["SettingId"]),
                                     LastRunDate = Convert.ToDateTime(recordset["LastRunDate"]),
                                     Password = Convert.ToString(recordset["Password"]),
                                     StartWithWindows = Convert.ToBoolean(recordset["Startup"]),
                                     View = (AutoShutdownView) Convert.ToInt32(recordset["ListView"]),
                                     WindowsState = (AutoShutdownWinState) Convert.ToInt32(recordset["WinState"])
                                 };
                recordset.Close();
            }

            return result;
        }

        
        public void Insert(History history)
        {
            string sql = @"INSERT INTO History (TaskId, Action, ActionDate, ActionHour, ActionMinute, " +
                         "Succeed, FilePath, Comment) VALUES (@TaskId, @Action, @ActionDate, @ActionHour, @ActionMinute, " +
                         "@Succeed, @FilePath, @Comment)";
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                object taskId = DBNull.Value;
                if (history.TaskId != 0) taskId = history.TaskId;
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@TaskId", Value = taskId });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Action", Value = history.Action });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@ActionDate", Value = history.When });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@ActionHour", Value = history.Hour });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@ActionMinute", Value = history.Minute });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Succeed", Value = history.Succeed ? 1 : 0 });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@FilePath", Value = history.FilePath });
                command.Parameters.Add(new SQLiteParameter() { ParameterName = "@Comment", Value = history.Comment });
                command.ExecuteNonQuery();
            }
        }


        public int GetLastTaskId()
        {
            string sql = "SELECT MAX(TaskId) FROM Tasks";
            int result = 0;
            using (SQLiteCommand command = new SQLiteCommand(databaseConnection))
            {
                command.CommandText = sql;
                object myResult = command.ExecuteScalar();
                result = myResult != DBNull.Value ? Convert.ToInt32(myResult) : 0;
            }
            return result;
        }
    }
}
